<!DOCTYPE html>
<html lang="zh-cn">
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">




<title>mysql练习题 | Miles`s</title>

<link rel="stylesheet" href="https://mada.gitee.io/notes//css/styles.css">

<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.2.0/css/all.css" 
integrity="sha384-hWVjflwFxL6sNzntih27bfxkr27PmbbK/iSvJ+a4+0owXq79v+lsFkW54bOGbiDQ" crossorigin="anonymous">
<script src="https://code.jquery.com/jquery-3.3.1.js" integrity="sha256-2Kok7MbOyxpgUVvAk/HJ2jigOSYS2auK4Pfzbm7uH60=" crossorigin="anonymous"></script>


<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/zenburn.min.css" crossorigin="anonymous" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js" integrity="sha256-/BfiIkHlHoVihZdc6TFuj7MmJ0TWcWsMXkeDFwhi0zw=" crossorigin="anonymous"></script>
<script>hljs.initHighlightingOnLoad();</script>
<script src="https://mada.gitee.io/notes//js/highlight.js"></script>






<div class="container">
    <nav class="navbar level">
      <div class="navbar-brand">
          <a class="nav-item" href="https://mada.gitee.io/notes/"><h1 class="title is-3">Miles`s</h1></a>
      </div>           
      <div class="navbar-menu has-text-centered is-active">
          <div class="navbar-end is-centered">
              
           </div>
      </div>
    </nav>
  </div>
<div class="container">
  <h2 class="subtitle is-6">July 8, 2016</h2>
  <h1 class="subtitle is-size-4-mobile is-size-3-desktop">mysql练习题</h1>
  <div class="content">
    

<h2 id="mysql-sql练习题-整理中-整合了网上了一些试题和面试书上内容">Mysql，Sql练习题（整理中，整合了网上了一些试题和面试书上内容）</h2>

<p>##测试数据 mysql</p>

<pre><code>create table Student(
Sid varchar(10),
Sname varchar(10),
Sage datetime,
Ssex varchar(10)
);
</code></pre>

<pre><code>insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
insert into Student values('09' , '孙吴昊' , '1990-01-20' , '女');
insert into Student values('10' , '赵雷' , '1990-01-20' , '女');
</code></pre>

<pre><code>create table Course(
Cid varchar(10),
Cname varchar(10),
Tid varchar(10)
);
</code></pre>

<pre><code>insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
insert into Course values('04' , '英语' , '01');
</code></pre>

<pre><code>create table Teacher(
Tid varchar(10),
Tname varchar(10)
);
</code></pre>

<pre><code>insert into Teacher values('01' , N'张三');
insert into Teacher values('02' , N'李四');
insert into Teacher values('03' , N'王五');
insert into Teacher values('04' , N'汪二蛋');
</code></pre>

<pre><code>create table SC(
Sid varchar(10),
Cid varchar(10),
score decimal(18,1)
);
</code></pre>

<pre><code>insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
insert into SC values('08' , '03' , 98);
insert into SC values('09' , '03' , 98);
insert into SC values('10' , '04' , 59);
</code></pre>

<h3 id="试题">试题</h3>

<h4 id="1-查询-01-课程比-02-课程成绩高的学生的信息及课程分数">1.查询&rdquo;01&rdquo;课程比&rdquo;02&rdquo;课程成绩高的学生的信息及课程分数</h4>

<p>答案一：
查询同时存在&rdquo;01&rdquo;课程和&rdquo;02&rdquo;课程的情况</p>

<pre><code>select a.*, b.score, c.score
from student a, sc b,sc c
where a.sid = b.sid and a.sid = c.sid 
and b.cid ='01' and c.cid = '02'
and b.score &gt; c.score;
</code></pre>

<h3 id="答案二-查询同时存在-01-课程和-02-课程的情况和存在-01-课程但可能不存在-02-课程的情况-不存在时显示为null">答案二： 查询同时存在&rdquo;01&rdquo;课程和&rdquo;02&rdquo;课程的情况和存在&rdquo;01&rdquo;课程但可能不存在&rdquo;02&rdquo;课程的情况(不存在时显示为null)</h3>

<pre><code>select a.*, b.score, c.score
from student a 
left join sc b on a.sid = b.sid and b.cid = '01'
left join sc c on a.sid = c.sid and c.cid = '02'
where b.score &gt; ifnull(c.score,0);
</code></pre>

<h3 id="2-查询平均成绩大于等于60分的同学的学生编号-学生姓名和平均成绩">2.查询平均成绩大于等于60分的同学的学生编号、学生姓名和平均成绩</h3>

<p>答案：</p>

<pre><code>select a.sid, a.sname, avg(sc.score) avg_score
from student a, sc
where a.sid = sc.sid
group by a.sid, a.sname
having avg_score &gt;= 60
order by a.sid;
</code></pre>

<h3 id="使用cast转换精度">使用cast转换精度</h3>

<pre><code>select a.sid, a.sname, avg(cast(sc.score as decimal(18,2))) avg_score
from student a, sc
where a.sid = sc.sid
group by a.sid, a.sname
having avg_score &gt;= 60
order by a.sid;
</code></pre>

<h3 id="3-查询平均成绩小于60分的同学的学生编号-学生姓名和平均成绩-包含无成绩的">3.查询平均成绩小于60分的同学的学生编号、学生姓名和平均成绩(包含无成绩的)</h3>

<pre><code>select a.sid, a.sname, avg(ifnull(sc.score,0)) avg_score
from student a left join sc
on a.sid = sc.sid
group by a.sname, a.sid
having avg_score &lt; 60
order by a.sid;
</code></pre>

<h3 id="4-查询所有同学的学生编号-学生姓名-选课总数-所有课程的总成绩">4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩</h3>

<p>查询所有有成绩的SQL</p>

<pre><code>select a.sid, a.sname, count(b.cid), sum(b.score)
from student a inner join sc b
on a.sid = b.sid
group by a.sid, a.sname
order by a.sid;
</code></pre>

<p>查询所有(包括有成绩和无成绩)的SQL</p>

<pre><code>select a.sid, a.sname, count(b.cid), sum(b.score)
from student a left join sc b
on a.sid = b.sid
group by a.sid, a.sname
order by a.sid;
</code></pre>

<h3 id="5-查询-李-姓老师的数量">5.查询&rdquo;李&rdquo;姓老师的数量</h3>

<pre><code>select count(tname)
from teacher
where tname like '李%';
</code></pre>

<h3 id="6-统计课程分01最高的前三名">6.统计课程分01最高的前三名</h3>

<pre><code>select student.sname, sc.score, sc.cid
from student join sc on student.sid = sc.sid 
where sc.cid='01'
order by sc.score desc limit 3;
</code></pre>

<h3 id="7-统计课程分最高的前三名-不分课程-分数的前三-人数可能大于三">7.统计课程分最高的前三名,不分课程(分数的前三，人数可能大于三)</h3>

<p>使用join</p>

<pre><code>select a.sname, a.score,  a.cid
from 
(select Student.sname, sc.score, sc.cid
from Student 
join SC on Student.Sid = SC.Sid) as a 
join 
(select distinct sc.score
from SC
order by score 
desc limit 3) as b
on a.score = b.score;
</code></pre>

<p>使用in</p>

<pre><code>select a.sname, a.score,  a.cid
from 
(select Student.sname, sc.score, sc.cid
from Student 
join SC on Student.Sid = SC.Sid) as a 
where a.score in
(select distinct sc.score from sc
order by sc.score
desc limit 3
);
</code></pre>

<h3 id="8-统计分数出现次数前三的分数">8.统计分数出现次数前三的分数</h3>

<pre><code>select score, count(*)
from SC
group by score
order by count(*) 
desc limit 3; 
</code></pre>

<h3 id="9-统计每个学生选课总数">9.统计每个学生选课总数</h3>

<pre><code>select a.sid, a.sname, count(b.cid) 
from student a left join sc b 
on a.sid=b.sid
group by a.sid,a.sname;
</code></pre>

<h3 id="10每个老师教多少学生-一个教师给某个学生教两门课-计入两次">10每个老师教多少学生（一个教师给某个学生教两门课，计入两次）</h3>

<pre><code>select t.tid, t.tname, a.countstu
from
(select c.tid as ctid, count(sc.cid) as countstu
from course c inner join sc
on c.cid = sc.cid
group by c.tid) a
right join teacher t
on t.tid = a.ctid;
</code></pre>

<p><code>From zhaodedong的博客</code>
<a href="http://blog.csdn.net/zhaodedong/article/details/49475059">地址</a></p>

  </div>
</div>
<div class="container has-text-centered">
    
</div>

<div class="container has-text-centered">
  
</div>
<section class="section">
  <div class="container has-text-centered">
    <p></p>
  </div>
</section>


